﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;

namespace ChinaDynastyTours.Controls
{
    class CashApplyCtrl
    {
        #region 导游付款申请信息查询
        
        /// <summary>
        /// 根据主键cashApplyId获取报销申请单数据信息
        /// </summary>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        internal static DataTable GetCashApplyDataById(string cashApplyId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = string.Format(@"SELECT * FROM fin_CashApply where CashApplyID ={0}", cashApplyId);

            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据指定团ID获取报销申请单信息
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static DataTable GetCashApplyDataByTourgroupID(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql="";
            if (string.IsNullOrEmpty(tourgroupId))
                Sql = "SELECT * FROM fin_CashApply";
            else
                Sql = string.Format(@"SELECT * FROM fin_CashApply where TourgroupID ='{0}'", tourgroupId);
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 根据指定团ID和付款申请状态为‘审核’通过，获取报销申请单信息
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static DataTable GetCashApplyDataByTourgroupIDAndPayStatus(string tourgroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string Sql = "";
            if (string.IsNullOrEmpty(tourgroupId))
                Sql = "SELECT * FROM fin_CashApply";
            else
                Sql = string.Format(@"SELECT * FROM fin_CashApply where TourgroupID='{0}' AND PayStatus='{1}'", tourgroupId, CashStatusEnum.已审核);
            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// 根据指定供应商ID获取付款申请单信息   
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns></returns>
        //internal static DataTable GetPayApplyDataByTourGroupID(string tourGroupId)
        //{

        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;

        //    string Sql = string.Format(@"SELECT * FROM vw_PayApply WHERE TourgroupID ='{0}'", tourGroupId);
        //    dbCommand = db.GetSqlStringCommand(Sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //}

        #endregion

        #region 付款申请增、删、改
        /// <summary>
        ///  创建报销申请数据记录
        /// </summary>
        /// <param name="payapplyEntity"></param>
        /// <returns>新增的报销申请单ID</returns>
        internal static string CreateCashApplyAndCashDetailData(CashApplyEntity cashApplyEntity, CashDetailEntity[] cashDetailEntitys)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO fin_CashApply (   [Contact], [TourgroupID],[TourgroupName],[TotalpaymentG], [TotalpaymentL], 
                                                                       [Applicants], [Audit], [ApplyPayTime], [PayTime], [PayStatus],[Remark])
                                                                       VALUES('{0}','{1}','{2}','{3}',{4},'{5}','{6}','{7}','{8}','{9}','{10}');Select SCOPE_IDENTITY();",
                                                                        cashApplyEntity.Contact,
                                                                        cashApplyEntity.TourgroupID,
                                                                        cashApplyEntity.TourgroupName,
                                                                        cashApplyEntity.TotalpaymentG,
                                                                        cashApplyEntity.TotalpaymentL,
                                                                        cashApplyEntity.Applicants,
                                                                        cashApplyEntity.Audit,
                                                                        cashApplyEntity.ApplyPayTime,
                                                                        cashApplyEntity.PayTime,
                                                                        cashApplyEntity.PayStatus,
                                                                        cashApplyEntity.Remark);
            string cashApplyId = db.ExecuteScalar(CommandType.Text, sql).ToString();

            //生成n条Insert语句，一并执行(生成一条报销申请单对应的多条付款项信息)
            StringBuilder sqlCashDetail = new StringBuilder();
            foreach (CashDetailEntity entity in cashDetailEntitys)
            {
                sqlCashDetail.AppendFormat(@" INSERT INTO fin_CashDetail([CashApplyID], [ExpenseID], [ApplyPay]) VALUES({0},{1},{2}); ",
                                cashApplyId,
                                entity.ExpenseID,
                                entity.ApplyPay);
            }

            db.ExecuteNonQuery(CommandType.Text, sqlCashDetail.ToString());

            return cashApplyId;
        }
        /// <summary>
        /// 报销申请单状态‘财务付款状态’修改‘1、提交2、审核3、付款’
        /// </summary>
        /// <param name="expenseEntity"></param>
        /// <param name="hotelEntity"></param>
        /// <returns></returns>
        internal static int UpdateCashApplyPayStatus(CashStatusEnum status, string cashApplyId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_CashApply SET [PayStatus]='{0}' WHERE CashApplyID={1}", status, cashApplyId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 报销申请单状态‘财务付款状态’修改‘1、提交2、审核3、付款’
        /// </summary>
        /// <param name="expenseEntity"></param>
        /// <param name="hotelEntity"></param>
        /// <returns></returns>
        internal static int UpdateCashApplyPayStatus(DateTime payTime, CashStatusEnum status, string voucher, string remark, string audit, string cashApplyId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_CashApply SET [payTime]='{0}', [PayStatus]='{1}', [Voucher]='{2}', [Remark]='{3}', [Audit]='{4}' WHERE CashApplyID={5}", payTime.ToString("yyyy-MM-dd"), status, voucher, remark, audit,cashApplyId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 更新凭证号
        /// </summary>
        /// <param name="voucher"></param>
        /// <param name="remark"></param>
        /// <param name="cashApplyId"></param>
        internal static int UpdateCashApplyVoucher(string voucher, string cashApplyId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fin_CashApply SET [Voucher]='{0}' WHERE CashApplyID={1}", voucher, cashApplyId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 根据付款ID号删除报销申请单及报销详情数据
        /// </summary>
        /// <param name="payApplyId"></param>
        /// <returns></returns>
        internal static int DeleteCashApplyAndCashDetail(string cashApplyId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM fin_CashApply WHERE CashApplyID={0};DELETE FROM fin_CashDetail WHERE CashApplyID={1}", cashApplyId, cashApplyId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        #endregion

    }
}
